package cn.edu.sjtu.literature.service.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import cn.edu.sjtu.literature.db.DBUtil;
import cn.edu.sjtu.literature.db.mapping.CategoryResultMappingCallback;
import cn.edu.sjtu.literature.db.mapping.LiteratureResultMappingCallback;
import cn.edu.sjtu.literature.entity.Category;
import cn.edu.sjtu.literature.entity.Literature;
import cn.edu.sjtu.literature.service.LiteratureQuery;
import cn.edu.sjtu.literature.service.LiteratureService;
import cn.edu.sjtu.literature.service.exception.LiteratureAlreadyExistsException;
import cn.edu.sjtu.literature.service.exception.SystemException;
import cn.edu.sjtu.literature.web.util.Constants;

public class LiteratureServiceImpl implements LiteratureService {

	private final Log log = LogFactory.getLog(this.getClass());
	
	@Override
	public void addLiterature(Literature literature)
			throws LiteratureAlreadyExistsException {
		String sql = "select l.*, c.id as categoryId, c.name as categoryName from t_literature l inner join t_category c on c.id = l.category_id where issue_no='" + literature.getIssueNo() + "'";
		Literature l = null;
		try {
			l = DBUtil.querySingle(sql, new LiteratureResultMappingCallback());
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			throw new SystemException();
		}
		if (l != null) {
			// this literature exists
			throw new LiteratureAlreadyExistsException("literature already exists");
		}
		
		sql = "insert into t_literature(issue_no, title, category_id, " +
				"author, authorFirm, authorContactInfo, source, publish_date, " +
				"created_date, created_by, modified_date, modified_by) " +
				"values('" + literature.getIssueNo() + "', " +
						"'" + literature.getTitle() + "', " +
						literature.getCategory().getId() + ", " +
						"'" + literature.getAuthor() + "', " +
						"'" + literature.getAuthorFirm() + "', " +
						"'" + literature.getAuthorContactInfo() + "', " +
						"'" + literature.getSource() + "', " +
						"'" + Constants.NORMAL_DATE_FORMAT.format(literature.getPublishDate()) + "', " +
						"'" + Constants.NORMAL_DATETIME_FORMAT.format(literature.getCreated()) + "', " +
						literature.getCreatedBy() + ", " +
						"'" + Constants.NORMAL_DATETIME_FORMAT.format(literature.getModified()) + "', " +
						literature.getModifiedBy() + ")";
		try {
			DBUtil.executeUpdate(sql);
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			throw new SystemException();
		}
	}
	
	@Override
	public Literature getLiterature(int id) {
		String sql = "select l.*, c.id as categoryId, c.name as categoryName from t_literature l inner join t_category c on c.id = l.category_id where l.id=" + id;
		Literature l = null;
		try {
			l = DBUtil.querySingle(sql, new LiteratureResultMappingCallback());
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			throw new SystemException();
		}
		return l;
	}
	
	@Override
	public void updateLiterature(Literature literature)
			throws LiteratureAlreadyExistsException {
		String sql = "select l.*, c.id as categoryId, c.name as categoryName from t_literature l inner join t_category c on c.id = l.category_id where issue_no = '" + literature.getIssueNo() + "'";
		Literature l = null;
		try {
			l = DBUtil.querySingle(sql, new LiteratureResultMappingCallback());
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			throw new SystemException();
		}
		if (l != null && l.getId() != literature.getId()) {
			// this literature exists
			throw new LiteratureAlreadyExistsException("literature already exists");
		}
		
		sql = "update t_literature set issue_no = '" + literature.getIssueNo() + "', " +
				"title = '" + literature.getTitle() + "', " +
				"category_id = " + literature.getCategory().getId() + ", " +
				"author = '" + literature.getAuthor() + "', " +
				"authorFirm = '" + literature.getAuthorFirm() + "', " +
				"authorContactInfo = '" + literature.getAuthorContactInfo() + "', " +
				"source = '" + literature.getSource() + "', " +
				"publish_date = '" + Constants.NORMAL_DATE_FORMAT.format(literature.getPublishDate()) + "', " +
				"modified_date = '" + Constants.NORMAL_DATETIME_FORMAT.format(literature.getModified()) + "', " +
				"modified_by = " + literature.getModifiedBy() + " " + 
				"where id = " + literature.getId();
		try {
			DBUtil.executeUpdate(sql);
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			throw new SystemException();
		}
	}

	@Override
	public List<Literature> queryLiteratures(LiteratureQuery query) {
		StringBuilder sb = new StringBuilder();
		sb.append("select l.*, c.id as categoryId, c.name as categoryName from t_literature l inner join t_category c on c.id = l.category_id ");
		sb.append("where 1=1 ");
		if (StringUtils.isNotBlank(query.getIssueNo())) {
			sb.append("and l.issue_no like '%" + query.getIssueNo() + "%' ");
		}
		if (StringUtils.isNotBlank(query.getTitle())) {
			sb.append("and l.title like '%" + query.getTitle() + "%' ");
		}
		if (query.getCategory() != null) {
			sb.append("and l.category_id = " + query.getCategory().getId() + " ");
		}
		if (StringUtils.isNotBlank(query.getAuthor())) {
			sb.append("and l.author like '%" + query.getAuthor() + "%' ");
		}
		if (StringUtils.isNotBlank(query.getAuthorFirm())) {
			sb.append("and l.authorFirm like '%" + query.getAuthorFirm() + "%' ");
		}
		if (StringUtils.isNotBlank(query.getAuthorContactInfo())) {
			sb.append("and l.authorContactInfo like '%" + query.getAuthorContactInfo() + "%' ");
		}
		if (StringUtils.isNotBlank(query.getSource())) {
			sb.append("and l.source like '%" + query.getSource() + "%' ");
		}
		if (query.getPublishDateFrom() != null) {
			sb.append("and l.publish_date >= '" + Constants.NORMAL_DATE_FORMAT.format(query.getPublishDateFrom()) + "' ");
		}
		if (query.getPublishDateTo() != null) {
			Calendar c = Calendar.getInstance();
			c.setTime(query.getPublishDateTo());
			c.add(Calendar.DAY_OF_YEAR, 1);
			sb.append("and l.publish_date < '" + Constants.NORMAL_DATE_FORMAT.format(c.getTime()) + "' ");
		}
		if (query.getCreatedDateFrom() != null) {
			sb.append("and l.created_date >= '" + Constants.NORMAL_DATE_FORMAT.format(query.getCreatedDateFrom()) + "' ");
		}
		if (query.getCreatedDateTo() != null) {
			Calendar c = Calendar.getInstance();
			c.setTime(query.getCreatedDateTo());
			c.add(Calendar.DAY_OF_YEAR, 1);
			sb.append("and l.created_date < '" + Constants.NORMAL_DATE_FORMAT.format(c.getTime()) + "' ");
		}
		sb.append("order by l.id");
		
		List<Literature> list = new ArrayList<Literature>();
		try {
			list = DBUtil.query(sb.toString(), new LiteratureResultMappingCallback());
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			throw new SystemException();
		}
		return list;
	}

	@Override
	public List<Category> getCategories() {
		List<Category> list = new ArrayList<Category>();
		try {
			list = DBUtil.query("select * from t_category order by id", new CategoryResultMappingCallback());
		} catch (SQLException e) {
			log.error(e.getMessage(), e);
			throw new SystemException();
		}
		return list;
	}

}
